package com.dangz.dans.service;

import com.alibaba.fastjson.JSON;
import com.dangz.dans.model.other.Columns;
import com.dangz.dans.model.other.Tables;
import com.dangz.dans.utils.JDBCUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.stereotype.Service;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * LocalServiceImpl 执行自定义sql
 * @author dangz
 * @since 2018/3/28 14:31
 */
@Service
public class LocalServiceImpl {
    @Autowired(required = false)
    private LocalContainerEntityManagerFactoryBean bean;

    private static final Logger logger = LoggerFactory.getLogger(LocalServiceImpl.class);

    //获取列
    /*ResultSetMetaData rsmd = rs.getMetaData();// 获取查询游标的元数据对象
            StringBuilder sb = new StringBuilder();
            System.out.println("列数目：" + rsmd.getColumnCount());
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                sb.append("列名：" + rsmd.getColumnName(i + 1) + " ");
                sb.append(rsmd.getColumnType(i + 1) + " ");
                sb.append(rsmd.getColumnLabel(i + 1) + " ");
                sb.append("列最大宽度：" + rsmd.getColumnDisplaySize(i + 1) + "个字符 ");
                sb.append("数据类型：" + rsmd.getColumnTypeName(i + 1));
                System.out.println(sb.toString());
                sb.delete(0, sb.length());
            }*/
    /**
     * MethonName: findAllTables
     * 查询MYSQL数据库连接所有的表
     * @params:  []
     * @return:  java.util.List<java.lang.String>
     *
     * @author: dangz
     * @since: 2018/3/28 14:47
     */
    public List<Tables> findAllTablesIsMysql(String url, String user, String pwd) throws Exception {
        List<Tables> tables = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        Connection con = JDBCUtils.setConnection(url, user, pwd, "mysql");
        try {
            StringBuilder sqlCom = new StringBuilder("SHOW TABLE STATUS");
            logger.info("执行sql：{}", sqlCom.toString());
            pre = con.prepareStatement(sqlCom.toString());
            rs = pre.executeQuery();
            tables = new ArrayList<>();
            Tables table = null;
            while (rs.next()) {
                table = new Tables();
                table.setName(rs.getString("Name"));
                table.setComment(rs.getString("Comment"));
                tables.add(table);
            }
        } finally {
            JDBCUtils.closeConnection(pre, rs);
        }
        return tables;
    }

    /**
     * MethonName: findAllTablesIsOracle
     * 查询Oracle数据库连接的所有表
     * @params:  []
     * @return:  java.util.List<com.dangz.dans.model.Tables>
     *
     * @author: dangz
     * @since: 2018/4/3 14:30
     */
    public List<Tables> findAllTablesIsOracle(String url, String user, String pwd) throws Exception {
        List<Tables> tables = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        Connection con = JDBCUtils.setConnection(url, user, pwd, "oracle");
        try {
            StringBuilder sqlCom = new StringBuilder("SELECT TABLE_NAME, COMMENTS FROM user_tab_comments");
            logger.info("执行sql：{}", sqlCom.toString());
            pre = con.prepareStatement(sqlCom.toString());
            rs = pre.executeQuery();
            tables = new ArrayList<>();
            Tables table = null;
            while (rs.next()) {
                table = new Tables();
                table.setName(rs.getString("TABLE_NAME"));
                table.setComment(rs.getString("COMMENTS"));
                tables.add(table);
            }
        } finally {
            JDBCUtils.closeConnection(pre, rs);
        }
        return tables;
    }

    public List<Columns> findFieldsByTableIsMysql(String url, String user, String pwd, String tableName) throws Exception {
        PreparedStatement pre = null;
        ResultSet rs = null;
        List<Columns> columns = null;
        Connection con = JDBCUtils.setConnection(url, user, pwd, "mysql");
        try {
            StringBuilder sqlCom = new StringBuilder("SHOW FULL COLUMNS FROM ");
            sqlCom.append(tableName);
            logger.info("执行sql：{}", sqlCom.toString());
            pre = con.prepareStatement(sqlCom.toString());
            rs = pre.executeQuery();
            columns = new ArrayList<>();
            Columns column = null;
            while (rs.next()) {
                column = new Columns();
                column.setField(rs.getString("Field"));
                column.setType(rs.getString("Type"));
                column.setDefaults(rs.getString("Default"));
                column.setKey(rs.getString("Key"));
                column.setNulls(rs.getString("Null"));
                column.setComment(rs.getString("Comment"));
                columns.add(column);
            }
        } finally {
            JDBCUtils.closeConnection(pre, rs);
        }
        return columns;
    }

    public List<Columns> findFieldsByTableIsOracle(String url, String user, String pwd, String tableName) throws Exception {
        List<Columns> columns = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        Connection con = JDBCUtils.setConnection(url, user, pwd, "oracle");
        String colKey = null;
        try {
            //获取主键
            StringBuilder sqlCom = new StringBuilder("SELECT col.COLUMN_NAME as COLUMN_NAME FROM USER_CONSTRAINTS con,  USER_CONS_COLUMNS col "
                    + " WHERE con.CONSTRAINT_NAME = col.CONSTRAINT_NAME "
                    + " AND con.CONSTRAINT_TYPE='P' AND con.TABLE_NAME = ");
            sqlCom.append("'").append(tableName).append("'");
            logger.info("执行sql：{}", sqlCom.toString());
            pre = con.prepareStatement(sqlCom.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ;
            rs = pre.executeQuery();
            if (rs.first()) {
                colKey = rs.getString("COLUMN_NAME");
            }
            //删除builder
            sqlCom.delete(0, sqlCom.length());

            sqlCom.append("SELECT t.COLUMN_NAME AS COLUMN_NAME, t.DATA_TYPE AS DATA_TYPE, "
                    + " t.DATA_LENGTH AS DATA_LENGTH, t.NULLABLE AS NULLABLE, t.DATA_DEFAULT AS DATA_DEFAULT, "
                    + " c.COMMENTS AS COMMENTS FROM user_tab_columns t "
                    + " LEFT JOIN user_col_comments c ON c.TABLE_NAME = t.TABLE_NAME AND c.COLUMN_NAME = t.COLUMN_NAME WHERE c.TABLE_NAME = ");
            sqlCom.append("'").append(tableName).append("'");
            logger.info("执行sql：{}", sqlCom.toString());
            pre = con.prepareStatement(sqlCom.toString());
            rs = pre.executeQuery();
            columns = new ArrayList<>();
            Columns column = null;
            while (rs.next()) {
                column = new Columns();
                column.setField(rs.getString("COLUMN_NAME"));
                if (column.getField().equals(colKey)) {
                    column.setKey("PRI");
                }
                column.setType(rs.getString("DATA_TYPE"));
                if (StringUtils.isNotEmpty(rs.getString("DATA_LENGTH"))) {
                    column.setType(column.getType() + "(" + rs.getString("DATA_LENGTH") + ")");
                }
                column.setDefaults(rs.getString("DATA_DEFAULT"));
//                column.setKey(rs.getString("key"));
                column.setNulls(rs.getString("NULLABLE"));
                column.setComment(rs.getString("COMMENTS"));
                columns.add(column);
            }
        } finally {
            JDBCUtils.closeConnection(pre, rs);
        }
        return columns;
    }

    /**
     * MethonName: findFieldsByTable
     * MYSQL根据表名查询表结构
     * @params:  [tableName]
     * @return:  java.util.List<com.dangz.dans.model.Columns>
     *
     * @author: dangz
     * @since: 2018/3/28 15:29
     */
    public List<Columns> findFieldsByTableIsMysql(String tableName){
        EntityManager manager = bean.getNativeEntityManagerFactory().createEntityManager();
        List<Columns> columns = null;
        try {
            StringBuilder sqlCom = new StringBuilder("SHOW FULL COLUMNS FROM ?");
            sqlCom.append(tableName);
            Query query = manager.createNativeQuery(sqlCom.toString());
//            query.setParameter(1, tableName);
            List<Object[]> result = query.getResultList();
            columns = setMysqlColumns(result);
        } finally {
            manager.close();
        }
        return columns;
    }

    /**
     * MethonName: findFieldsByTableIsOracle
     * 查询Oracle表结构
     * @params:  [tableName]
     * @return:  java.util.List<com.dangz.dans.model.Columns>
     *
     * @author: dangz
     * @since: 2018/4/3 14:29
     */
    public List<Columns> findFieldsByTableIsOracle(String tableName){
        EntityManager manager = bean.getNativeEntityManagerFactory().createEntityManager();
        List<Columns> columns = null;
        try {
            StringBuilder sqlCom = new StringBuilder("SELECT t.COLUMN_NAME, t.DATA_TYPE, t.DATA_LENGTH, t.NULLABLE, t.DATA_DEFAULT, c.COMMENTS FROM user_tab_columns t" +
                    " LEFT JOIN user_col_comments c ON c.TABLE_NAME = t.TABLE_NAME WHERE c.TABLE_NAME = ?");
            sqlCom.append(tableName);
            Query query = manager.createNativeQuery(sqlCom.toString());
//            query.setParameter(1, tableName);
            List<Object[]> result = query.getResultList();
            columns = setOracleColumns(result);
        } finally {
            manager.close();
        }
        return columns;
    }

    /**
     * MethonName: setColumns
     * 封装Mysql表结构
     * @params:  [columns]
     * @return:  java.util.List<com.dangz.dans.model.Columns>
     *
     * @author: dangz
     * @since: 2018/3/28 15:28
     */
    private List<Columns> setMysqlColumns(List<Object[]> columns){
        List<Columns> tableColumns = new ArrayList<>();
        Columns column = null;
        Object[] obj = null;
        for (int i = 0, count = columns.size(); i < count; i++) {
            column = new Columns();
            obj = columns.get(i);
            column.setField(obj[0].toString());
            column.setType(obj[1].toString());
            column.setNulls(obj[3].toString());
            if (obj[4] != null) {
                column.setKey(obj[4].toString());
            }
            if (obj[5] != null) {
                column.setDefaults(obj[5].toString());
            }
            if (obj[8] != null) {
                column.setComment(obj[8].toString());
            }
            tableColumns.add(column);
        }
        return  tableColumns;
    }

    /**
     * MethonName: setColumns
     * 封装Oracle表结构
     * @params:  [columns]
     * @return:  java.util.List<com.dangz.dans.model.Columns>
     *
     * @author: dangz
     * @since: 2018/3/28 15:28
     */
    private List<Columns> setOracleColumns(List<Object[]> columns){
        List<Columns> tableColumns = new ArrayList<>();
        Columns column = null;
        Object[] obj = null;
        for (int i = 0, count = columns.size(); i < count; i++) {
            column = new Columns();
            obj = columns.get(i);
            column.setField(obj[0].toString());
            column.setType(obj[1].toString());
            if (obj[2] != null) {
                column.setType(obj[1].toString() + "(" + obj[2].toString() + ")");
            }
            column.setNulls(obj[3].toString());
            /*if (obj[3] != null) {
                column.setKey(obj[3].toString());
            }*/
            if (obj[4] != null) {
                column.setDefaults(obj[4].toString());
            }
            if (obj[5] != null) {
                column.setComment(obj[5].toString());
            }
            tableColumns.add(column);
        }
        return  tableColumns;
    }
}
